import os
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from copy import copy
Base = declarative_base()
# 定义Maping对象:
class Maping(Base):
    # 表的名字:
    __tablename__ = 'maping'

    # 表的结构:
    dict_name =  Column(String(100))
    key = Column(String(100), primary_key=True)
    value = Column(String(500))



engine = create_engine('postgresql+psycopg2://test:1qaz@WSX@localhost:5432/eurostat')

if __name__ == '__main__':
    floder = 'en'
    files = os.listdir(floder)
    Base.metadata.create_all(engine)
    DBSession = sessionmaker(bind=engine)
    session = DBSession()
    error=[]
    for file in files:
        try:
            if os.path.splitext(file)[1] == '.dic':  # 目录下包含.json的文件
                with open(os.path.join(floder, file)) as read_file:
                    print("process " + file + " ...")
                    for item in read_file:
                        item = item.replace("\t"," ").replace("\n"," ").split(" ",1)
                        new_mapping = Maping()
                        new_mapping.dict_name = file
                        new_mapping.key = copy(item[0]).strip()
                        new_mapping.value = copy(item)
                        try:
                            session.add(new_mapping)
                            session.commit()
                        except Exception as e:
                            error.append(item)
                            session.rollback()

                        finally:
                            del new_mapping
        except Exception as e:
            print(e)
    print(error)



